﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

using FundSms.Client.IDAL;
using FundSms.Client.Model;
using FundSms.DBUtility;

namespace FundSms.Client.OracleDAL
{
    public class SmsReturn : ISmsReturn
    {
        #region ISmsReturn 成员
        private const string SQL_INSERT_RETURN = "INSERT INTO TSMS_RETURN_HUNDSUN(C_RETURN,C_MOBILE,C_CONTENT,C_PROPERTY,C_FROM,C_SMSNAME,C_SMSID,C_DATE,C_R1,C_R2,C_R3,C_R4,C_R5) VALUES (:C_RETURN,:C_MOBILE,:C_CONTENT,:C_PROPERTY,:C_FROM,:C_SMSNAME,:C_SMSID,:C_DATE,:C_R1,:C_R2,:C_R3,:C_R4,:C_R5)";
        private const string SQL_INSERT_RETURN_BATCH = "INSERT INTO TSMS_RETURN_HUNDSUN(C_RETURN,C_MOBILE,C_CONTENT,C_PROPERTY,C_FROM,C_SMSNAME,C_SMSID,C_DATE,C_R1,C_R2,C_R3,C_R4,C_R5) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
        private const string SQL_UPDATE_RETURN = "UPDATE TSMS_RETURN_HUNDSUN SET C_RETURN=:STATUS WHERE C_SMSID=:C_SMSID and C_MOBILE=:C_MOBILE";
        private const string SQL_SELECT_COUNT_SENDCOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= :RETURNTIME AND c_returntime < :NEXTRETURNTIME";
        private const string SQL_SELECT_COUNT_RETURNCOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= :RETURNTIME AND c_returntime < :NEXTRETURNTIME  AND c_return <> ''";
        private const string SQL_SELECT_COUNT_UNRETURNCOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= :RETURNTIME AND c_returntime < :NEXTRETURNTIME AND c_return = ''";
        private const string SQL_SELECT_COUNT_SUCCESSCOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= :RETURNTIME AND c_returntime < :NEXTRETURNTIME AND c_return = '0'";
        private const string SQL_SELECT_COUNT_FAILURECOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= :RETURNTIME AND c_returntime < :NEXTRETURNTIME AND c_return <> '0' AND c_return <> ''";

        private const string PARM_C_RETURN = ":C_RETURN";
        private const string PARM_C_MOBILENO = ":C_MOBILE";
        private const string PARM_C_CONTENT = ":C_CONTENT";
        private const string PARM_C_PROPERTY = ":C_PROPERTY";
        private const string PARM_C_FROM = ":C_FROM";
        private const string PARM_C_SMSNAME = ":C_SMSNAME";
        private const string PARM_C_SMSID = ":C_SMSID";
        private const string PARM_C_DATE = ":C_DATE";

        private const string PARM_RETURNTIME = ":RETURNTIME";
        private const string PARM_NEXTRETURNTIME = ":NEXTRETURNTIME";

        private const string PARM_C_STATUS = ":STATUS";

        private const string PARM_C_R1 = ":C_R1";
        private const string PARM_C_R2 = ":C_R2";
        private const string PARM_C_R3 = ":C_R3";
        private const string PARM_C_R4 = ":C_R4";
        private const string PARM_C_R5 = ":C_R5";

        public void Insert(TSMSReturnHundSunInfo returninfo)
        {
            OracleParameter[] logparms = GetReturnParameters();

            logparms[0].Value = returninfo.CReturn;
            logparms[1].Value = returninfo.CMobile;
            logparms[2].Value = returninfo.CContent;
            logparms[3].Value = returninfo.CProperty;
            logparms[4].Value = returninfo.CFrom;
            logparms[5].Value = returninfo.CSmsName;
            logparms[6].Value = returninfo.CSmsID;
            logparms[7].Value = returninfo.CDate;
            logparms[8].Value = returninfo.CR1;
            logparms[9].Value = returninfo.CR2;
            logparms[10].Value = returninfo.CR3;
            logparms[11].Value = returninfo.CR4;
            logparms[12].Value = returninfo.CR5;

            OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_INSERT_RETURN, logparms);
        }

        public void InsertBatch(string sql)
        {
            OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringClientSms, CommandType.Text, sql, null);
        }

        public void InsertList(List<TSMSReturnHundSunInfo> li)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("BEGIN");

            object[] obj = new object[11];

            int i = 1;

            foreach (TSMSReturnHundSunInfo loginfo in li)
            {
                obj[0] = loginfo.CReturn;
                obj[1] = loginfo.CMobile;
                obj[2] = loginfo.CContent;
                obj[3] = loginfo.CProperty;
                obj[4] = loginfo.CFrom;
                obj[5] = loginfo.CSmsName;
                obj[6] = loginfo.CSmsID;
                obj[7] = loginfo.CDate;
                obj[8] = loginfo.CR1;
                obj[9] = loginfo.CR2;
                obj[10] = loginfo.CR3;
                obj[11] = loginfo.CR4;
                obj[12] = loginfo.CR5;

                sb.Append(string.Format(SQL_INSERT_RETURN_BATCH, obj));
                sb.Append(";");

                i++;
            }

            sb.Append("END");

            OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringClientSms, CommandType.Text, sb.ToString(), null);
        }

        public void Update(TSMSReturnHundSunInfo returninfo)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void UpdateStatus(string status, string smsid, string mobile)
        {
            OracleParameter[] logparms = GetReturnUpdateParameters();

            logparms[0].Value = status;
            logparms[1].Value = smsid;
            logparms[2].Value = mobile;

            OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_UPDATE_RETURN, logparms);
        }

        /// <summary>
        /// 获得当前日期下的已发短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetSendCount(DateTime datetime)
        {
            OracleParameter[] parms = new OracleParameter[2];

            parms[0] = new OracleParameter(PARM_RETURNTIME, OracleType.VarChar, 50);
            parms[1] = new OracleParameter(PARM_NEXTRETURNTIME, OracleType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)OracleHelper.ExecuteScalar(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_SENDCOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得指定日期下已经返回的短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetReturnCount(DateTime datetime)
        {
            OracleParameter[] parms = new OracleParameter[2];

            parms[0] = new OracleParameter(PARM_RETURNTIME, OracleType.VarChar, 50);
            parms[1] = new OracleParameter(PARM_NEXTRETURNTIME, OracleType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)OracleHelper.ExecuteScalar(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_RETURNCOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得指定日期下未返回的短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetUnReturnCount(DateTime datetime)
        {
            OracleParameter[] parms = new OracleParameter[2];

            parms[0] = new OracleParameter(PARM_RETURNTIME, OracleType.VarChar, 50);
            parms[1] = new OracleParameter(PARM_NEXTRETURNTIME, OracleType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)OracleHelper.ExecuteScalar(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_UNRETURNCOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得指定日期下成功的短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetSuccessCount(DateTime datetime)
        {
            OracleParameter[] parms = new OracleParameter[2];

            parms[0] = new OracleParameter(PARM_RETURNTIME, OracleType.VarChar, 50);
            parms[1] = new OracleParameter(PARM_NEXTRETURNTIME, OracleType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)OracleHelper.ExecuteScalar(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_SUCCESSCOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得失败情况下的短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetFailureCount(DateTime datetime)
        {
            OracleParameter[] parms = new OracleParameter[2];

            parms[0] = new OracleParameter(PARM_RETURNTIME, OracleType.VarChar, 50);
            parms[1] = new OracleParameter(PARM_NEXTRETURNTIME, OracleType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)OracleHelper.ExecuteScalar(OracleHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_FAILURECOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得缓存的参数
        /// </summary>
        /// <returns></returns>
        private static OracleParameter[] GetReturnParameters()
        {
            OracleParameter[] parms = OracleHelper.GetCachedParameters(SQL_INSERT_RETURN);

            if (parms == null)
            {
                parms = new OracleParameter[] {												 
                    new OracleParameter(PARM_C_RETURN, OracleType.Char, 1),
					new OracleParameter(PARM_C_MOBILENO, OracleType.VarChar, 13),
					new OracleParameter(PARM_C_CONTENT, OracleType.VarChar, 140),
					new OracleParameter(PARM_C_PROPERTY, OracleType.VarChar,2),
					new OracleParameter(PARM_C_FROM, OracleType.VarChar, 20),
					new OracleParameter(PARM_C_SMSNAME, OracleType.VarChar, 36),
					new OracleParameter(PARM_C_SMSID, OracleType.VarChar, 50),
					new OracleParameter(PARM_C_DATE, OracleType.VarChar, 50),
					new OracleParameter(PARM_C_R1, OracleType.VarChar, 50),
					new OracleParameter(PARM_C_R2, OracleType.VarChar, 50),
                    new OracleParameter(PARM_C_R3, OracleType.VarChar, 50),
                    new OracleParameter(PARM_C_R4, OracleType.VarChar, 50),
                    new OracleParameter(PARM_C_R5, OracleType.VarChar, 50)};

                OracleHelper.CacheParameters(SQL_INSERT_RETURN, parms);
            }

            return parms;
        }

        /// <summary>
        /// 获得缓存的参数
        /// </summary>
        /// <returns></returns>
        private static OracleParameter[] GetReturnUpdateParameters()
        {
            OracleParameter[] parms = OracleHelper.GetCachedParameters(SQL_UPDATE_RETURN);

            if (parms == null)
            {
                parms = new OracleParameter[] {												 
					new OracleParameter(PARM_C_STATUS, OracleType.Char, 1),
					new OracleParameter(PARM_C_SMSID, OracleType.VarChar, 50),
                    new OracleParameter(PARM_C_MOBILENO, OracleType.VarChar, 13)
            };

                OracleHelper.CacheParameters(SQL_UPDATE_RETURN, parms);
            }

            return parms;
        }

        #endregion
    }
}
